﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;

namespace LiteExpress.Core.DbClient
{
    /// <summary>
    /// 表字段结构
    /// </summary>
    public sealed class DbColumnInfo
    {
        /// <summary>
        /// 是否主键
        /// </summary>
        public bool IsPrimaryKey { get; set; }
        /// <summary>
        /// 字段名称
        /// </summary>
        public string ColumnName { get; set; }
        /// <summary>
        /// 字段类型
        /// </summary>
        public string DbType { get; set; }
        /// <summary>
        /// 数据库类型对应的C#类型
        /// </summary>
        public string CsharpType { get; set; }

        /// <summary>
        /// 是否自增列
        /// </summary>
        public bool IsIdentity { get; set; }

        /// <summary>
        /// 是否允许空
        /// </summary>
        public bool IsNullable { get; set; }

        /// <summary>
        /// 是否为计算列
        /// </summary>
        public bool IsComputed { get; set; }

        /// <summary>
        /// 描述
        /// </summary>
        public string Remark { get; set; }
    }

    /// <summary>
    /// 表结构
    /// </summary>
    public sealed class DbTableInfo
    {
        /// <summary>
        /// 表名称
        /// </summary>
        public string TableName { get; set; }
        /// <summary>
        /// 表的注释
        /// </summary>
        public string Comment { get; set; }
    }

    public class DbInfoUtilities
    {
        /// <summary>
        /// 获取数据库中所有表名。当前只支持mssql, mysql
        /// </summary>
        public static IEnumerable<string> GetTables(string dbName)
        {
            return DbFactory.GetClient(dbName, (conn) =>
            {
                string sql = @"SELECT
                    obj.name Name, p.value Comment
                    from sys.objects obj 
					left join sys.extended_properties p on obj.object_id=p.major_id and p.name = 'MS_Description'
                    where obj.type='U' AND obj.name <> 'sysdiagrams'
                    order by obj.name";                
                if (conn is MySql.Data.MySqlClient.MySqlConnection)
                {
                    sql = "SHOW TABLE STATUS;";
                }
                return conn.Query<string>(sql);
            });
        }

        /// <summary>
        /// 获取表中所有字段信息。当前只支持mssql, mysql
        /// </summary>
        private static IEnumerable<DbColumnInfo> GetMsSqlColumns(string dbName, string tableName)
        {
           var result = DbFactory.GetClient(dbName, (conn) =>
           {
               string sql = string.Format(@"
                    WITH indexCTE AS
                    (
                    SELECT 
                    ic.column_id,
                    ic.index_column_id,
                    ic.object_id    
                    FROM sys.indexes idx
                    INNER JOIN sys.index_columns ic ON idx.index_id = ic.index_id AND idx.object_id = ic.object_id
                    WHERE  idx.object_id =OBJECT_ID('{0}') AND idx.is_primary_key=1
                    )
                    select
                    (CASE WHEN indexCTE.column_id IS NULL THEN 0 ELSE 1 END) IsPrimaryKey,
                    colm.name ColumnName,
                    systype.name ColumnType,
                    CONVERT(int,colm.is_identity) IsIdentity,
                    CONVERT(int,colm.is_nullable) IsNullable,
                    CONVERT(int,colm.is_computed) IsComputed,
                    prop.value Remark
                    from sys.columns colm
                    inner join sys.types systype on colm.system_type_id=systype.system_type_id and colm.user_type_id=systype.user_type_id
                    left join sys.extended_properties prop on colm.object_id=prop.major_id and colm.column_id=prop.minor_id
                    LEFT JOIN indexCTE ON colm.column_id=indexCTE.column_id AND colm.object_id=indexCTE.object_id                                        
                    where colm.object_id=OBJECT_ID('{0}')
                    order by colm.column_id", tableName);

               if (conn is MySql.Data.MySqlClient.MySqlConnection)
               {
                   sql = string.Format(@"SELECT COLUMN_NAME AS ColumnName ,DATA_TYPE AS ColumnType, 
                         (CASE WHEN IS_NULLABLE = 'YES' THEN 1 ELSE 0 END) AS IsNullable,
                         (CASE WHEN COLUMN_KEY = 'PRI' THEN 1 ELSE 0 END) AS IsPrimaryKey,
                         (CASE WHEN EXTRA = 'auto_increment' THEN 1 ELSE 0 END) AS IsIdentity,
                            0 AS IsComputed,
                         COLUMN_COMMENT AS Remark	
                        FROM information_schema.`COLUMNS` 
                        WHERE TABLE_SCHEMA = '{1}' AND TABLE_NAME = '{0}'", tableName, conn.Database);
               }

               return conn.Query<DbColumnInfo>(sql);
           });

            foreach (var item in result)
            {
                item.CsharpType = DbTypeToCSharpType(item.DbType);
            }
            return result;
        }

        /// <summary>
        /// 返回数据库字段对应的C#变量类型
        /// </summary>        
        private static string DbTypeToCSharpType(string dbType)
        {
            switch (dbType)
            {
                case "bigint":
                    return "long";
                case "bit":
                    return "bool";
                case "binary":
                    return "object";
                case "datetime":
                case "smalldatetime":
                    return "DateTime";
                case "float":
                    return "double";
                case "image":
                case "timestamp":
                case "varbinary":
                    return "byte[]";
                case "money":
                case "smallmoney":
                case "numeric":
                    return "decimal";
                case "real":
                    return "float";
                case "smallint":
                    return "short";
                case "char":
                case "nchar":
                case "varchar":
                case "nvarchar":
                case "text":
                case "ntext":
                    return "string";
                case "tinyint":
                    return "byte";
                case "uniqueidentifier":
                    return "Guid";
                default:
                    return dbType;
            }
        }
    }
}
